﻿
package cn.zniot.so.database;

import cn.zniot.so.application.constant.Constants;
import cn.zniot.so.application.data.User;
import cn.zniot.so.application.util.MethodUtil;
import cn.zniot.so.transport.asynpack.LogicCotrolPacketD;

import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 数据库操作管理器
 */
public class DBManager extends AbstractDAO {

    private final Logger logger = Logger.getLogger(DBManager.class);
    private static DBManager instance = null;

    private DBManager() {

    }

    public synchronized static DBManager getInstance() {
        if (instance == null) {
            instance = new DBManager();
        }
        return instance;
    }

    /**
     * 得到数据库的一个连接
     */
    public static Connection getConnection() throws Exception {
        Connection conn = ConnectionMgr.getInstance().getConnection();
        return conn;
    }

    /**
     * 更新最近uplast_box上报数据表记录
     */
    private boolean uplast_box(LogicCotrolPacketD packet) throws Exception {

        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        int num = 0;
        boolean uplast_box=false;
        
        String insertsql = "insert into userinfo "
                + " (box_id,ip,online,online_time)"
                + " values(?, ?, ?,?)";
        
        String updatesql = "update userinfo "
                + " set ip = ?, online = ?,outline_time=?"
                + " where box_id = ? ";
        
        String updatesq2 = "update userinfo "
                + " set ip = ?, online = ?,online_time=?"
                + " where box_id = ? ";
        
        String updatesq3 = "update mobileuser "
                + "set online = ?,online_time=?"
                + " where username = ? and password=?";
        
        String updatesq4 = "update mobileuser "
                + "set online = ?,outline_time=?"
                + " where box_id = ? ";
        
        String selectMobileuser="select count(1) from mobileuser where username=? and password=?";
        
        try {
            conn = DBManager.getConnection();
            String desc = packet.getData2();
            String imei = packet.getImei();
            if (desc == null && imei!=null && imei.startsWith(Constants.SLEEP_SEND_COMMAND)) {
                stmt = conn.prepareStatement(updatesq4);
                stmt.setString(1, packet.getPdString());
                stmt.setString(2, MethodUtil.getDateTime("yyyy-MM-dd HH:mm:ss"));
                stmt.setString(3, packet.getImei().substring(1));
                stmt.executeUpdate();
                uplast_box = true;
            }else if (desc==null && imei!=null && imei.startsWith(Constants.FAIL_CONNECT)) {
                num = this.isExistId(packet, conn);
                if (num>0) {
                    stmt = conn.prepareStatement(updatesql);
                    stmt.setString(1, packet.getUid());
                    stmt.setString(2, packet.getPdString());
                    stmt.setString(3, MethodUtil.getDateTime("yyyy-MM-dd HH:mm:ss"));
                    stmt.setString(4, packet.getImei().substring(1));
                    stmt.executeUpdate();
                    uplast_box=true;
                }
            }
            else if(desc!=null && desc.equals(Constants.ID_HEAD)) {
                //插入手机用户表
                int onlineId = this.isOnlineId(packet, conn);
                if (onlineId>0) {
                    //用户表中是否有此用户的记录
                    stmt = conn.prepareStatement(selectMobileuser);
                    stmt.setString(1, packet.getData1());
                    stmt.setString(2, packet.getData5());
                    rs = stmt.executeQuery();
                    if (rs.next()) {
                        num = rs.getInt(1);
                    }if (num>0) {
                    	//更新手机表中的用户信息
                        stmt = conn.prepareStatement(updatesq3);
                        stmt.setString(1, packet.getPdString());
                        stmt.setString(2, MethodUtil.getDateTime("yyyy-MM-dd HH:mm:ss"));
                        stmt.setString(3, packet.getData1());
                        stmt.setString(4, packet.getData5());
                        stmt.executeUpdate();
                        uplast_box=true;
                    }else {
                        uplast_box=false;
                    }
                    
                }else {
                    //这个盒子没有上线或者是根本没有在盒子表中存在的情况
                    uplast_box=false;
                }
            }else if (desc!=null && desc.equals(Constants.DEFAUT_VALUE)){
                // 判断盒子在最近记录表中是否有记录
                num = this.isExistId(packet, conn);
                if (num > 0) {
                    // 下线和更新
                    if (packet.getPdString().equals(Constants.SINGE_NO)) {
                        stmt = conn.prepareStatement(updatesql);
                    } else {
                        stmt = conn.prepareStatement(updatesq2);
                    }
                    stmt.setString(1, packet.getUid());
                    stmt.setString(2, packet.getPdString());
                    stmt.setString(3, MethodUtil.getDateTime("yyyy-MM-dd HH:mm:ss"));
                    stmt.setString(4, packet.getImei().substring(1));
                } else {
                    stmt = conn.prepareStatement(insertsql);
                    stmt.setString(1, packet.getImei().substring(1));
                    stmt.setString(2, packet.getUid());
                    stmt.setString(3, packet.getPdString());
                    stmt.setString(4, MethodUtil.getDateTime("yyyy-MM-dd HH:mm:ss"));
                }
                stmt.executeUpdate();
                uplast_box=true;
            }
        }catch (SQLException e) {
            uplast_box=false;
            logger.error(e);
            throw new Exception("更新或者添加数据异常", e);
        } finally {
            this.closeResource(conn, stmt, rs);
        }
        return uplast_box;
    }

 // 注册业务
    public String registService(User user) throws SQLException
    {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
//        MD5 md5 = new MD5();
        String sql = "insert into mobileuser "
                + " (box_id,password,online,online_time,username,email) "
                + "values('"
                + user.getBox_id().substring(1)
                + "',"
                + "'"
                + user.getPass()
                + "','"
                + Constants.SINGE_YES
                + "',"
                + "'"
                + MethodUtil.getDateTime("yyyy-MM-dd HH:mm:ss")
                + "',"
                + "'"
                + user.getName()
                + "',"
                + "'"
                + user.getEmail()
                +
                "')";
        try
        {
            conn = ConnectionMgr.getInstance().getConnection();
            conn.setAutoCommit(false);
            st = conn.prepareStatement(sql);
            st.executeUpdate(sql);
            conn.commit();
            return user.getName();
        } catch (Exception e)
        {
            e.printStackTrace();
            conn.rollback();
        } finally
        {
            this.closeResource(null, st, rs);
        }
        return null;
    }
    
    /**
     * 判断盒子在最近记录表中是否有记录
     */
    private int isExistId(LogicCotrolPacketD rtp, Connection conn)
            throws Exception {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = "select count(1) num from userinfo where box_id = ?";
        int num = 0;
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, rtp.getImei().substring(1));
            rs = stmt.executeQuery();
            if (rs.next()) {
                num = rs.getInt(1);
            }
        } catch (SQLException e) {
            logger.error(e);
            throw new Exception("判断盒子在最近记录表中是否有记录异常", e);
        } finally {
            this.closeResource(null, stmt, rs);
        }
        return num;
    }
    
    /**
     * 判断盒子在最近记录表中是否有记录
     */
    public String isExistUsername(String rtp) throws Exception {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = "select box_id from mobileuser where username = ?";
        String num = null;
        Connection conn = null;
        try {
            conn = DBManager.getConnection();
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, rtp);
            rs = stmt.executeQuery();
            if (rs.next()) {
                num = rs.getString(1);
            }
        } catch (SQLException e) {
            logger.error(e);
            throw new Exception("判断用户在用户表中是否存在异常", e);
        } finally {
            this.closeResource(null, stmt, rs);
        }
        return num;
    }


    /**
     * 判断数据库中在线盒子的用户数
     */
    private int isOnlineId(LogicCotrolPacketD rtp, Connection conn)
            throws Exception {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String onlineBox = "select count(1) num from userinfo where box_id = ? and online='"+Constants.SINGE_YES+"'";
        int num = 0;
        try {
            stmt = conn.prepareStatement(onlineBox);
            stmt.setString(1, rtp.getImei().substring(1));
            rs = stmt.executeQuery();
            if (rs.next()) {
                num = rs.getInt(1);
            }
        } catch (SQLException e) {
            logger.error(e);
            throw new Exception("判断盒子是否在线异常", e);
        } finally {
            this.closeResource(null, stmt, rs);
        }
        return num;
    }

    /**
     * fanmingzheng
     * 保存Zniot数据到数据库
     */
    public boolean saveZniot2DB(LogicCotrolPacketD packet)
    {
        boolean uplast_box = false;
        try
        {
            uplast_box= uplast_box(packet);
        } catch (Exception e)
        {
            logger.error(MethodUtil.printExceptionErr(e));
        }
        return uplast_box;
    }
    /**
   	 * 用户登录
   	 * 
   	 * @return 用户ID
   	 */
   	public String simpleLogin(String userName, String pass){
   		String userId = null;
   		Connection conn = null;
   		PreparedStatement st = null;
   		ResultSet rs = null;
   		String sql = " select t.box_id from mobileuser t where t.username = ? and t.password = ?";
   		
   		try {
   			conn = DBManager.getConnection();
   			st= conn.prepareStatement(sql);
   			st.setString(1, userName);
   			st.setString(2, pass);
   			rs = st.executeQuery();
   			if (rs.next()) {
   				userId = rs.getString(1);
   			}
   		} catch (Exception e) {
   			logger.error(e);
   		} finally {
   			DBManager.getInstance().closeResource(conn, st, rs);
   		}
   		return userId;		
   	}
   	
   	 /**
   		 * 重设密码
   		 * 
   		 * @return 用户ID
   		 */
   		public String findPassword(String userName,String paString,String resetPassword){
   			String boxId = null;
   			Connection conn = null;
   			PreparedStatement st = null;
   			ResultSet rs = null;
   			String sql = " select t.box_id from mobileuser t where t.username = ? and t.password=?";
   			String updatePassword = "update mobileuser set password = ? where username = ? ";
   			try {
   				conn = DBManager.getConnection();
   				st= conn.prepareStatement(sql);
   				st.setString(1, userName);
   				st.setString(2, paString);
   				rs = st.executeQuery();
   				if (rs.next()) {
   					boxId = rs.getString(1);
   				}
   				if (boxId!=null) {
   					 st = conn.prepareStatement(updatePassword);
                        st.setString(1, resetPassword);
                        st.setString(2, userName);
                        st.executeUpdate();
   				}
   			} catch (Exception e) {
   				logger.error(e);
   			} finally {
   				DBManager.getInstance().closeResource(conn, st, rs);
   			}
   			return boxId;		
   		}
   		
   		
}
